﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using BEELab.Data;

namespace BEELab.Logic.ProgramManagement
{
    public static class FunctionManagement
    {
        /// <summary>
        /// Gets function details, Creates new function and inserts it to DB
        /// </summary>
        /// <param name="Name">Function Name</param>
        /// <param name="Description">Function Description</param>
        /// <param name="OwnerID">Function OwnerID</param>
        /// <param name="isVisible">Function isVisible property</param>
        /// <param name="CreateSystemDefaults">If true - will put inside function default values</param>
        /// <returns>Number of raws effected on DB</returns>
        public static int CreateFunction(string Name, string Description, int OwnerID, Boolean isVisible, Boolean CreateSystemDefaults)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "INSERT INTO Programs (Name, Description, OwnerID, isVisible, Type, Permission) VALUES (@Name, @Description, @OwnerID, @isVisible, @Type, @Permission); SELECT SCOPE_IDENTITY();";
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Description", Description));
            command.Parameters.Add(new SqlParameter("@OwnerID", OwnerID));
            command.Parameters.Add(new SqlParameter("@isVisible", isVisible));
            command.Parameters.Add(new SqlParameter("@Type", "Function"));
            command.Parameters.Add(new SqlParameter("@Permission", "Private"));

            int ID = int.Parse(DBHelper.ExecuteScalar(command));

            if (CreateSystemDefaults)
            {
                int SettingsID = StageManagement.StageManagement.CreateStage(ID, "Settings", "System stage for the function settings page", true, false, false);
                BEELab.Logic.ProgramManagement.StageManagement.PresentationElements.CreateElement(SettingsID, Properties.Settings.Default.PresentationLayer + SettingsID, "Presetation Layer", "Your Presentation Layer");
                StageManagement.StageManagement.CreateStage(ID, "Execution", "System stage for the actual function content", true, true, true);
            }

            return ID;
        }

        public static int DuplicateFunction(int FunctionID, string NewName,  string NewDescription, int OwnerID, Boolean isVisible)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Name FROM Programs WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", FunctionID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            if (dt.Rows.Count != 1)
                return -1;

            DataRow dr = dt.Rows[0];

            int ID = CreateFunction(NewName, NewDescription, OwnerID, isVisible, false);

            Dictionary<int, int> VariablesDictionary = new Dictionary<int, int>();

            foreach (int id in ProgramManagement.VariableManagement.VariableManagement.ListByProgramID(FunctionID))
            {
                VariablesDictionary[id] = ProgramManagement.VariableManagement.VariableManagement.ImportVariable(id, ID, "");
            }

            foreach (int id in ProgramManagement.StageManagement.StageManagement.ListByProgramID(FunctionID))
            {
                command = new SqlCommand();
                command.CommandText = "SELECT Name, Description FROM Stages WHERE ID=@ID";
                command.Parameters.Add(new SqlParameter("@ID", id));
                dt = DBHelper.ExecuteQuery(command);

                if (dt.Rows.Count != 1)
                    return -1;

                int NewStageID;
                dr = dt.Rows[0];
                if (dr["Name"].ToString() != "Settings")
                {
                    NewStageID = ProgramManagement.StageManagement.StageManagement.CreateStage(ID, dr["Name"].ToString(), dr["Description"].ToString(), true, true, true);
                }
                else
                {
                    NewStageID = ProgramManagement.StageManagement.StageManagement.CreateStage(ID, dr["Name"].ToString(), dr["Description"].ToString(), true, false, false);
                }

                ProgramManagement.StageManagement.StageManagement.ImportStageIntoStage(id, NewStageID, 0, "", true, true, VariablesDictionary);
            }

            return ID;
        }

        public static int GetExecutionStageID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Stages WHERE ProgramID=@ProgramID AND Name='Execution'";
            command.Parameters.Add(new SqlParameter("@ProgramID", ID));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        public static int GetSettingsStageID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Stages WHERE ProgramID=@ProgramID AND Name='Settings'";
            command.Parameters.Add(new SqlParameter("@ProgramID", ID));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        public static string GetName(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Name FROM Programs WHERE ID=@ProgramID";
            command.Parameters.Add(new SqlParameter("@ProgramID", ID));
            return DBHelper.ExecuteScalar(command);
        }

        public static string GetDescription(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Description FROM Programs WHERE ID=@ProgramID";
            command.Parameters.Add(new SqlParameter("@ProgramID", ID));
            return DBHelper.ExecuteScalar(command);
        }

        public static Boolean DeleteFunction(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "DELETE FROM Programs WHERE ID=@ID AND NOT Exists(SELECT ID FROM Containers WHERE FunctionID=@ID); UPDATE Programs SET isVisible='false' WHERE ID=@ID; DELETE FROM Programs WHERE Type='Function' AND isVisible='false' AND NOT EXISTS (SELECT ID FROM Containers WHERE FunctionID=Programs.ID)";
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res > 0)
                return true;

            return false;
        }

        /// <summary>
        /// Updates function details on DB - Name, Description, Owner and Visiblity
        /// </summary>
        /// <param name="ID">Function ID</param>
        /// <param name="Name">Function New Name</param>
        /// <param name="Description">Function New </param>
        /// <param name="OwnerID">Function New </param>
        /// <param name="isVisible">Function isVisble property</param>
        /// <returns>True if succeeded, False otherwise</returns>
        public static Boolean UpdateFunction(int ID, string Name, string Description, int OwnerID, Boolean isVisible)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE Programs SET Name=@Name, Description=@Description, OwnerID=@OwnerID, isVisible=@isVisible WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Description", Description));
            command.Parameters.Add(new SqlParameter("@OwnerID", OwnerID));
            command.Parameters.Add(new SqlParameter("@isVisible", isVisible));
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        public static List<int> ListByUserID(int ID)
        {
            List<int> result = new List<int>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Programs WHERE OwnerID=@OwnerID AND Type='Function'";
            command.Parameters.Add(new SqlParameter("@OwnerID", ID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((int)dr["ID"]);
            }

            return result;
        }

        public static Boolean ChangePermission(int ID, string Permission, string Comment)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE Programs SET Permission=@Permission, AdminComment=@AdminComment WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@Permission", Permission));
            command.Parameters.Add(new SqlParameter("@AdminComment", Comment));
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        public static int GetOwnerID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT OwnerID FROM Programs WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            string res = DBHelper.ExecuteScalar(command);

            if (res != null && res.Length > 0)
            {
                return int.Parse(res);
            }

            return 0;
        }
    }
}